﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;

namespace DAO
{
   public class NguyenVatLieuDB
    {
        DataProvider dp = new DataProvider();
        DataTable dt = new DataTable();
        public DataTable dLayDanhSachNVLThucDon(int maTuan, string thu)
        {
           string sql;
               sql=" select nvl.MaNL,nvl.TenNL,nvl.DonVi,sum(manl.SoLuongMotPhan) as 'TSLCD',nvl.SoLuongTon,nvl.LoaiNL ";
           sql=sql+" from CTThucDon cttd,DichVu dv,MonAnNguyenLieu manl,NguyenVatLieu nvl ";
           sql=sql+" where cttd.MaDV=dv.MaDV ";
           sql=sql+" and manl.MaDV=dv.MaDV ";
           sql=sql+" and nvl.MaNL=manl.MaNL ";
           sql=sql+" and cttd.Thu='"+thu+"' ";
           sql=sql+" and cttd.MaTuan="+maTuan ;
           sql=sql+" group by nvl.MaNL,nvl.TenNL,nvl.DonVi,nvl.SoLuongTon,nvl.LoaiNL ";
           return dt = dp.ExecuteQuery(sql);
        }
        public DataTable dLayDanhSachNVLAll()
        {
            string sql = "select * from NguyenVatLieu";
            return dt = dp.ExecuteQuery(sql);
        }
        public DataTable dLayDanhSachNVL(int MaDV)
        {
            string sql = "select nvl.TenNL, manl.SoLuongMotPhan,nvl.MaNL from NguyenVatLieu nvl, MonAnNguyenLieu manl where nvl.MaNL=manl.MaNL and MaDV  = '" + MaDV + "'";
            return dt = dp.ExecuteQuery(sql);
        }
        public DataTable dao_danhsachnguyenlieu(string tennguyenlieu)
        {
            string sql = "select nvl.TenNL, nvl.SoLuongTon, nvl.DonVi, nvl.LoaiNL, cttd.SoPhanBan,nvl.MaNL,  sum((cttd.SoPhanBan*manl.SoLuongMotPhan)) as N'Tổng SL Cần' from NguyenVatLieu nvl, CTThucDon cttd, ThucDonDuTru tddt, MonAnNguyenLieu manl where nvl.MaNL=manl.MaNL and manl.MaDV=cttd.MaDV and cttd.MaTuan=tddt.MaTuan and nvl.TenNL='" + tennguyenlieu + "' group by nvl.TenNL, nvl.SoLuongTon, nvl.DonVi, nvl.LoaiNL,  cttd.SoPhanBan, nvl.MaNL";
            dt = dp.ExecuteQuery(sql);
            return dt;
        }
        public DataTable dLayDSNVLTuoiSong(string ngay)
        {
            string sql = "select p.MaPhieuYC,p.NVLapPhieuYC,p.NgayYeuCau,ctp.MaNL,ctp.SoLuongYC,nvl.DonVi,nvl.LoaiNL,nvl.SoLuongTon,nvl.TenNL,nvl.HanSuDung,nv.TenNV,nv.MaNV ";
            sql = sql + "from PhieuYeuCauNL p,CTPhieuYeuCauNL ctp ,NguyenVatLieu nvl, NhanVien nv ";
            sql = sql + "where p.MaPhieuYC=ctp.MaPhieuYC ";
            sql = sql + "and nv.MaNV=p.NVLapPhieuYC ";
            sql = sql + "and ctp.MaNL=nvl.MaNL ";
            sql = sql + "and p.NgayYeuCau=" + "'" + ngay + "' ";
            sql = sql + "and nvl.LoaiNL=0";
            return dt = dp.ExecuteQuery(sql);
        }
        public DataTable dLayDSNVLDaiHan(string ngay)
        {
            string sql = "select p.MaPhieuYC,p.NVLapPhieuYC,p.NgayYeuCau,ctp.MaNL,ctp.SoLuongYC,nvl.DonVi,nvl.LoaiNL,nvl.SoLuongTon,nvl.TenNL,nvl.HanSuDung,nv.TenNV ";
            sql = sql + "from PhieuYeuCauNL p,CTPhieuYeuCauNL ctp ,NguyenVatLieu nvl,NhanVien nv ";
            sql = sql + "where p.MaPhieuYC=ctp.MaPhieuYC ";
            sql = sql + "and nv.MaNV=p.NVLapPhieuYC ";
            sql = sql + "and ctp.MaNL=nvl.MaNL ";
            sql = sql + "and p.NgayYeuCau=" + "'" + ngay + "' ";
            sql = sql + "and nvl.LoaiNL=1";
            return dt = dp.ExecuteQuery(sql);
        }
    }
}
